﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using Bilyteam_Website;
using ClassLibrary1.Entity;


namespace ClassLibrary1.DAO
{
    public class ProvicesDAO : SQLDBHelper
    {
        private SqlCommand cmd;
        private SqlDataReader reader;
        private IList<Provices> provicesList;
        public ProvicesDAO()
        {
            strConn = ConfigurationManager.ConnectionStrings["PROJECT"].ConnectionString;
        }

        #region SQL Queries

        public const string SQl_SELECT_PROVICES = "SELECT * FROM Provices WHERE Status = 0 ORDER BY ProviceID  ";
        public const string SQL_SELECT_PROVICES_BY_ID = "SELECT * FROM Provices WHERE ProviceID = @ProviceID ";
        public const string SQL_INSERT_PROVICES = "INSERT INTO Provices(ProviceID,ProviceName,Status) VALUES(@ProviceID,@ProviceName,@Status)";
        public const string SQL_COUNT_PROVICES = "SELECT COUNT (*) FROM Provices ";
        public const string SQL_UPDATE_PROVICES = "UPDATE Provices SET ProviceName = @ProviceName WHERE ProviceID=@ProviceID ";
        public const string SQL_DELETE_PROVICES = "DELETE FROM Provices WHERE ProviceID=@ProviceID ";

        #endregion

        #region Parameters

        private const string PARM_PROV_ID = "@ProviceID";
        private const string PARM_PROV_NAME = "@ProviceName";
        private const string PARM_PROVE_STATUS = "@Status";
        private SqlConnection conn;
        private string strConn;

        #endregion

        private Provices ProvicesManipulate(SqlDataReader reader)
        {
            Provices prov = new Provices();
            if (!reader.IsDBNull(0))
                prov.ProviceID = reader.GetString(0);
            if (!reader.IsDBNull(1))
                prov.ProviceName = reader.GetString(1);
            if (!reader.IsDBNull(2))
                prov.Status = reader.GetBoolean(2);

            return prov;

        }
        public Provices GetProvByID(string id)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter(PARM_PROV_ID, SqlDbType.NVarChar) };
            parms[0].Value = id;
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_SELECT_PROVICES_BY_ID, parms))
            {
                if (reader.Read())
                {
                    Provices news = ProvicesManipulate(reader);
                    return news;
                }
            }
            return null;
        }

        public IList<Provices> GetAll()
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter("@Status", SqlDbType.Bit) };
            parms[0].Value = 0;
            IList<Provices> provlist = new List<Provices>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQl_SELECT_PROVICES, parms))
            {
                while (reader.Read())
                {
                    Provices prov = ProvicesManipulate(reader);
                    provlist.Add(prov);
                }

            }
            return provlist;
        }

        public bool Insert(Provices prov)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[]
                       {
                           new SqlParameter(PARM_PROV_ID,SqlDbType.NVarChar), 
                           new SqlParameter(PARM_PROV_NAME,SqlDbType.NVarChar),
                           new SqlParameter(PARM_PROVE_STATUS,SqlDbType.Bit)};
            parms[0].Value = prov.ProviceID;
            parms[1].Value = prov.ProviceName;
            parms[2].Value = prov.Status;

            int result = 0;
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            result = SQLDBHelper.ExecuteNonQuery(conn,
                CommandType.Text, SQL_INSERT_PROVICES, parms);
            if (conn.State == ConnectionState.Open)
                conn.Close();
            return (result > 0 ? true : false);

            return false;
        }
        public bool Update(Provices prov)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[]
                        {
                            new SqlParameter(PARM_PROV_ID, SqlDbType.NVarChar),
                            new SqlParameter(PARM_PROV_NAME, SqlDbType.NVarChar),
                            new SqlParameter(PARM_PROVE_STATUS,SqlDbType.Bit), 
                            
                        };

            parms[0].Value = prov.ProviceID;
            parms[1].Value = prov.ProviceName;
            parms[2].Value = prov.Status;

            try
            {
                int result = 0;
                SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
                result = SQLDBHelper.ExecuteNonQuery(conn,
                    CommandType.Text, SQL_UPDATE_PROVICES, parms);
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                return (result > 0 ? true : false);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return false;

        }
        public bool Delete(string id)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter(PARM_PROV_ID, SqlDbType.NVarChar), };
            new SqlParameter(PARM_PROVE_STATUS, SqlDbType.Bit);
            parms[0].Value = id;

            try
            {
                int result = 0;
                result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                    CommandType.Text, SQL_DELETE_PROVICES,
                                                     parms);
                return (result > 0 ? false : true);
            }
            catch (Exception e)
            {

                throw e;
            }
            return false;
        }

    }
}